package com.example.myapplication;
/*
    用于数据库操作的类。
    只有LoginActivity需要用到getWritableDatabase，其他Activity想要获取数据库只需要调用该类中的getDatabase()方法即可。
 */
import static android.database.sqlite.SQLiteDatabase.OPEN_READWRITE;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;
import android.widget.Toast;

public class MyDataBaseHelper extends SQLiteOpenHelper {

    private final String Tag = "MyDataBaseHelper";

    //初始数据库信息
    database_data d = new database_data();
    private user_info[] stu_info = d.getStu_info();
    private user_info[] tea_info = d.getTea_info();
    private user_info[] admin_info = d.getAdmin_info();
    private classroom_info[] classroom_info1 = d.getClassroom_info1();
    private classroom_info[] classroom_info2 = d.getClassroom_info2();
    private classroom_info[] classroom_info3 = d.getClassroom_info3();

    //用于打开数据库
    public static SQLiteDatabase getDatabase(){
        SQLiteDatabase db = SQLiteDatabase.openDatabase("/data/data/com.example.myapplication/databases/test72.dp",null, OPEN_READWRITE);
        if(db != null){
            return db;
        }else{
            return null;
        }
    }

    //建表語句
    public static final String CREATE_STU = "Create table stu("+
            "id text primary key,"+
            "name text,"+
            "pwd text)";
    //建表語句
    public static final String CREATE_TEA = "Create table tea("+
            "id text primary key,"+
            "name text,"+
            "pwd text)";
    public static final String CREATE_ADMIN = "Create table admin("+
            "id text primary key,"+
            "name text,"+
            "pwd text)";
    public static final String CREATE_1 = "Create table classroom1("+
            "name_building text,"+
            "id_classroom text,"+
            "class1 text,"+
            "class2 text,"+
            "class3 text,"+
            "class4 text," +
            "primary key (name_building,id_classroom))";
    public static final String CREATE_2 = "Create table classroom2("+
            "name_building text,"+
            "id_classroom text,"+
            "class1 text,"+
            "class2 text,"+
            "class3 text,"+
            "class4 text," +
            "primary key (name_building,id_classroom))";
    public static final String CREATE_3 = "Create table classroom3("+
            "name_building text,"+
            "id_classroom text,"+
            "class1 text,"+
            "class2 text,"+
            "class3 text,"+
            "class4 text," +
            "primary key (name_building,id_classroom))";
    private Context mContext;
    public MyDataBaseHelper(Context context, String name, SQLiteDatabase.CursorFactory factory,
                            int version){
        super(context,name,factory,version);
        mContext=context;
    }
    @Override
    public void onCreate(SQLiteDatabase db) {

        Log.d("MyDataBaseHelper","数据库创建中");
        Toast.makeText(mContext,"creating",Toast.LENGTH_SHORT).show();
        db.beginTransaction();

        try{
            /*
            创建数据库
             */
            db.execSQL(CREATE_STU);
            db.execSQL(CREATE_TEA);
            db.execSQL(CREATE_ADMIN);
            db.execSQL(CREATE_1);
            db.execSQL(CREATE_2);
            db.execSQL(CREATE_3);

        }catch (Exception e){
            e.printStackTrace();
            Toast.makeText(mContext,"create failure",Toast.LENGTH_SHORT).show();
            Log.d("MyDataBaseHelper","表创建失败");
        }

        try{


            /*
            7.1尝试用事务方式提交数据
             */


            //学生表
            String sql_stu = "insert into stu(id,name,pwd) values (?,?,?)";
            SQLiteStatement stat_stu = db.compileStatement(sql_stu);
            for(user_info info : stu_info){
                stat_stu.bindString(1,info.getId());
                stat_stu.bindString(2,info.getName());
                stat_stu.bindString(3,info.getPwd());
                stat_stu.executeInsert();
            }



            //教师表
            String sql_tea = "insert into tea(id,name,pwd) values (?,?,?)";
            SQLiteStatement stat_tea = db.compileStatement(sql_tea);

            for(user_info info : tea_info){
                stat_tea.bindString(1,info.getId());
                stat_tea.bindString(2,info.getName());
                stat_tea.bindString(3,info.getPwd());
                stat_tea.executeInsert();
            }


            //管理员表
            String sql_admin = "insert into admin(id,name,pwd) values (?,?,?)";
            SQLiteStatement stat_admin = db.compileStatement(sql_admin);

            for(user_info info : admin_info){
                stat_admin.bindString(1,info.getId());
                stat_admin.bindString(2,info.getName());
                stat_admin.bindString(3,info.getPwd());
                stat_admin.executeInsert();
            }



            //向classroom6_27插入数据
            String sql_1 = "insert into classroom1(name_building,id_classroom,class1,class2,class3,class4) " +
                    "values (?,?,?,?,?,?)";
            SQLiteStatement stat_1 = db.compileStatement(sql_1);
            for(classroom_info info : classroom_info1){
                stat_1.bindString(1,info.getName_building());
                stat_1.bindString(2,info.getId_classroom());
                stat_1.bindString(3,info.getClass1());
                stat_1.bindString(4,info.getClass2());
                stat_1.bindString(5,info.getClass3());
                stat_1.bindString(6,info.getClass4());
                stat_1.executeInsert();
            }


            //向classroom6_28插入数据
            String sql_2 = "insert into classroom2(name_building,id_classroom,class1,class2,class3,class4) " +
                    "values (?,?,?,?,?,?)";
            SQLiteStatement stat_2 = db.compileStatement(sql_2);
            for(classroom_info info : classroom_info2){
                stat_2.bindString(1,info.getName_building());
                stat_2.bindString(2,info.getId_classroom());
                stat_2.bindString(3,info.getClass1());
                stat_2.bindString(4,info.getClass2());
                stat_2.bindString(5,info.getClass3());
                stat_2.bindString(6,info.getClass4());
                stat_2.executeInsert();
            }


            //向classroom6_29插入数据
            String sql_3 = "insert into classroom3(name_building,id_classroom,class1,class2,class3,class4) " +
                    "values (?,?,?,?,?,?)";
            SQLiteStatement stat_3 = db.compileStatement(sql_3);
            for(classroom_info info : classroom_info3){
                stat_3.bindString(1,info.getName_building());
                stat_3.bindString(2,info.getId_classroom());
                stat_3.bindString(3,info.getClass1());
                stat_3.bindString(4,info.getClass2());
                stat_3.bindString(5,info.getClass3());
                stat_3.bindString(6,info.getClass4());
                stat_3.executeInsert();
            }

            stat_1.close();
            stat_2.close();
            stat_3.close();
        }catch(Exception e){
            e.printStackTrace();
            Toast.makeText(mContext,"create failure",Toast.LENGTH_SHORT).show();
            Log.d("MyDataBaseHelper","数据库创建失败");
        }finally {
            db.setTransactionSuccessful();
            db.endTransaction();
            Log.d("MyDataBaseHelper","数据库创建完成");
            Toast.makeText(mContext,"create success",Toast.LENGTH_SHORT).show();
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("drop table if exists stu");
        db.execSQL("drop table if exists tea");
        db.execSQL("drop table if exists admin");
        db.execSQL("drop table if exists classroom627");
        db.execSQL("drop table if exists classroom628");
        db.execSQL("drop table if exists classroom629");
        db.execSQL("drop table if exists classroom1");
        db.execSQL("drop table if exists classroom2");
        db.execSQL("drop table if exists classroom3");

        Log.d(Tag,"重新创建表，来自onCreate");

        onCreate(db);
    }

}
